package com.btpn.custody.dao.hibernate;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;

import com.btpn.custody.common.Constant;
import com.btpn.custody.dao.IMasterData;
import com.btpn.custody.entity.MasterData;

@Repository("masterDataDaoHibernate")
public class MasterDataDAO implements IMasterData {
	private final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(this.getClass());
	protected HibernateTemplate template = null;

	@Autowired @Required
	public void setSessionFactory(SessionFactory sessionFactory) {
		template = new HibernateTemplate(sessionFactory);
	}

	@Override
	public MasterData get(String sid) {
		return template.get(MasterData.class, sid);
	}

	@Override
	public List<MasterData> findAll() {
		return template.loadAll(MasterData.class);
	}

	@Override
	public Integer getCount() {
		return Integer.valueOf(findAll().size());
	}

	@Override
	public Serializable create(MasterData masterData) {
		return template.save(masterData);
	}

	@Override
	public MasterData update(MasterData masterData) {
		template.update(masterData);
		return masterData;
	}

	@Override
	public MasterData merge(MasterData masterData) {
		template.merge(masterData);
		return masterData;
	}

	// alternative for merge
	@Override
	public MasterData updateCallback(final MasterData masterData) {
		template.execute(new HibernateCallback<Void>() {

	        @Override
	        public Void doInHibernate(Session session) throws HibernateException, SQLException {
	        	MasterData toBeUpdated = (MasterData) session.get(MasterData.class, masterData.getSid());

	            if (toBeUpdated != null){

	                // WARNING : change values for certain fields
	            	toBeUpdated.setUpdatedDate(masterData.getUpdatedDate());

	                session.update(toBeUpdated);
	            }
	            return null;
	        }
	    });
		return masterData;
	}

	@Override
	public void createOrUpdate(MasterData masterData) {
		template.saveOrUpdate(masterData);
	}

	@Override
	public void delete(String sid) {
		template.delete(get(sid));
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByStatus(String status) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__STATUS, new String[] {"status"}, new Object[] {status});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByStatuses(String status1, String status2) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__STATUSES_2, new String[] {"status1", "status2"}, new Object[] {status1, status2});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByStatuses(String status1, String status2, Date from, Date to) {
		logger.info("find by status 3: "+status1+"|"+status2+"|"+from+"|"+to);
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__STATUSES_3, new String[] {"status1", "status2","from","to"}, new Object[] {status1, status2, from, to});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByAreaIdStatuses(Long areaId, String status1, String status2) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__AREA_ID_WITH_2_STATUS, new String[] {"areaId", "status1", "status2"}, new Object[] {areaId, status1, status2});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByAreaIdStatusesDate(Long areaId, String status1, String status2, Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__AREA_ID_WITH_2_STATUS_DATE,
				new String[] {"areaId", "status1", "status2", "from", "to"}, new Object[] {areaId, status1, status2, from, to});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findAllByDateSend(String status1, String status2, Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__ALL_BY_DATE_SEND,
				new String[] {"status1", "status2", "from", "to"}, new Object[] {status1, status2, from, to});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByBranchIdStatusesDate(Long branchId, String status1, String status2, Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__BRANCH_ID_WITH_2_STATUS_DATE,
				new String[] {"branchId", "status1", "status2", "from", "to"}, new Object[] {branchId, status1, status2, from, to});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByNasabahType(String nasabahType) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__NASABAH_TYPE, new String[] {"nasabahType"}, new Object[] {nasabahType});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByBranchId(Long branchId) {

		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__BRANCH_ID, "branchId", branchId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByBranchIdWithStatus(Long branchId, String status) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__BRANCH_ID_WITH_STATUS, new String[] {"branchId", "status"}, new Object[] {branchId, status});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByNotInStatuses2DateRange(String status1, String status2, Date from, Date to) {
		if ((from!=null) && (to!=null)) {
			return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__NOT_IN_STATUSES_2_DATE_RANGE,
					new String[] {"status1", "status2", "from", "to"}, new Object[] {status1, status2, from, to});
		} else {
			return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__NOT_IN_STATUSES_2,
					new String[] {"status1", "status2"}, new Object[] {status1, status2});
		}
	}

//	@SuppressWarnings("unchecked")
//	@Override
//	public List<MasterData> findByNotInStatuses3(String status1, String status2, String status3) {
//		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__NOT_IN_STATUSES_3,
//				new String[] {"status1", "status2", "status3"}, new Object[] {status1, status2, status3});
//	}

//	@SuppressWarnings("unchecked")
//	@Override
//	public List<MasterData> findByBranchIdWithNotInStatuses2(Long branchId, String status1, String status2) {
//
//		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__BRANCH_ID_WITH_NOT_IN_STATUSES_2,
//				new String[] {"branchId", "status1", "status2"}, new Object[] {branchId, status1, status2});
//	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByBranchIdWithNotInStatuses3DateRange(String status1, String status2, String status3, Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__NOT_IN_STATUSES_3_DATE_RANGE,
				new String[] {"status1", "status2", "status3", "from", "to"}, new Object[] {status1, status2, status3, from, to});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> search(Long areaId, String status, String keyword, String branchCode, String accCode, String cstCode, Date from, Date to) {
		System.out.println("AreaId : " + areaId);
		System.out.println("Status : " + status);
		System.out.println("Keyword : '" + keyword + "'");
		System.out.println("BranchCode : " + branchCode);
		System.out.println("AccCode : " + accCode);
		System.out.println("CstCode : " + cstCode);
		System.out.println("From : " + from);
		System.out.println("To : " + to);

		Criteria cMD = template.getSessionFactory().getCurrentSession().createCriteria(MasterData.class, "md");
		cMD.createAlias("CIF", "c");
		cMD.createAlias("accounts", "ac");						// WARNING : can produce duplicate data
		Criteria cBranch = cMD.createCriteria("branch", "b");	// allias only at 'ujung'
		cBranch.createAlias("area", "a");


		// for keyword
		if (!StringUtils.isBlank(keyword)) {
			Disjunction disj = Restrictions.disjunction();		// make OR condition
			disj.add(Restrictions.like("c.CUST_FULL_NAME", "%"+keyword+"%"));
			disj.add(Restrictions.like("ac.ACCOUNT", "%"+keyword+"%"));
			cMD.add(disj);
		}


		// in areaId
		cMD.add(Restrictions.eq("a.id", areaId));

		// in STATUS_MD
		if (status.equals(Constant.STATUS__MD_RECEIVED)) {
			cMD.add(Restrictions.in("STATUS_MD", new Object[] { Constant.STATUS__MD_TBO, Constant.STATUS__MD_RECEIVED }));
			System.out.println("STATUS_MD: "+Constant.STATUS__MD_TBO+" "+Constant.STATUS__MD_RECEIVED);
		} else {
			cMD.add(Restrictions.eq("STATUS_MD", status));
			System.out.println("STATUS_MD: "+ status);
		}

		// for branch
		if (!branchCode.equals("-1")) {
			cMD.add(Restrictions.eq("b.id", Long.parseLong(branchCode)));
		}

		// for cstType
		if (!cstCode.equals("-1")) {
			// criteria can't as 'md.c.CUSTOMER_TYPE'
			cMD.add(Restrictions.eq("c.CUSTOMER_TYPE", cstCode));
		}

		// for accType
		if (!accCode.equals("-1")) {
			cMD.add(Restrictions.eq("ac.ACCOUNT_TYPE", accCode));
		}

		// for date
		if ((from==null) && (to!=null)) {						// from last until to
			cMD.add(Restrictions.le("ac.ACCOUNT_OPEN", DateUtils.addDays(to, 1)));
		} else if ((from!=null) && (to==null)) {				// from from until now
			cMD.add(Restrictions.ge("ac.ACCOUNT_OPEN", DateUtils.addSeconds(from, -1)));
		} else if ((from != null) && (to != null)) {
			cMD.add(Restrictions.between("ac.ACCOUNT_OPEN", from, DateUtils.addSeconds(DateUtils.addDays(to, 1), -1)));
        }
		System.out.println("jumlah list : "+cMD.list().size());

		cMD.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        return cMD.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public MasterData getByCif(String cif) {
		List<MasterData> result = template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__CIF, new String[] {"cif"}, new Object[] {cif});

		if (result.size()>0) {
			return result.get(0);
		} else {
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByBranchIdWith2Status(Long branchId, String status1, String status2) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__BRANCH_ID_WITH_2_STATUS,
				new String[] {"branchId", "status1", "status2"}, new Object[] {branchId, status1, status2});

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findByStatusBranchCode(String status, String branchCode) {
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__STATUS_BRANCHCODE,
				new String[] {"status", "branchCode"}, new Object[] {status, branchCode});
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<MasterData> findBySIDStatuses(String status1, String status2,
			String sid) {
		
		return template.findByNamedQueryAndNamedParam(MasterData.NQ__FIND_BY__SID__STATUS,
				new String[] {"status1", "status2","sid"}, new Object[] {status1, status2, sid});
	}
	
	
	@SuppressWarnings("unchecked")
	public List<MasterData> findByAccount(String status,String Status2, Date from, Date to) {
		Criteria cMD = template.getSessionFactory().getCurrentSession().createCriteria(MasterData.class, "md");
		
		cMD.createAlias("accounts", "ac");		
		cMD.add(Restrictions.between("ac.ACCOUNT_OPEN", from, to));
		
//		cMD.add(Restrictions.between("ac.ACCOUNT_OPEN", from, DateUtils.addSeconds(DateUtils.addDays(to, 1), -1)));
		
		cMD.add(Restrictions.in("STATUS_MD", new Object[] { status, Status2}));
	
		
		cMD.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		
		System.out.println("======> jumlah list : "+cMD.list().size());
	    return cMD.list();
	}
}